from pyecharts.charts import Page
import pandas as pd
import sys
sys.path.append("..//database")
from SqlWizard import *
from CmnFuncs import *

def run_oil():
    '''
    本模块包含品种：豆油、棕榈油、菜油
    设计图表：
    1. 豆油现货基差表；
    2. 豆油月差表；
    3. 棕榈油现货基差表；
    4. 棕榈油月差表；
    5. 菜油现货基差表；
    6. 菜油月差表；
    7. 豆棕价差；
    8. 豆菜价差；
    9. 菜棕价差；
    '''

    a_list = date_checker_1()

    y_spot = read_sql_spot('spot_y')
    y_index = read_sql_fut_index('fut_index_y')
    y_ry = read_sql_ry('roll_yield_y')
    y_spdrdx = read_sql_spdrdx('spdrdex_y')
    y_1 = read_sql_fut('fut_y{}'.format(a_list[0]))
    y_2 = read_sql_fut('fut_y{}'.format(a_list[1]))
    y_3 = read_sql_fut('fut_y{}'.format(a_list[2]))

    p_spot = read_sql_spot('spot_p')
    p_index = read_sql_fut_index('fut_index_p')
    p_ry = read_sql_ry('roll_yield_p')
    p_spdrdx = read_sql_spdrdx('spdrdex_p')
    p_1 = read_sql_fut('fut_p{}'.format(a_list[0]))
    p_2 = read_sql_fut('fut_p{}'.format(a_list[1]))
    p_3 = read_sql_fut('fut_p{}'.format(a_list[2]))

    oi_spot = read_sql_spot('spot_oi')
    oi_index = read_sql_fut_index('fut_index_oi')
    oi_ry = read_sql_ry('roll_yield_oi')
    oi_spdrdx = read_sql_spdrdx('spdrdex_oi')
    oi_1 = read_sql_fut('fut_oi{}'.format(a_list[0]))
    oi_2 = read_sql_fut('fut_oi{}'.format(a_list[1]))
    oi_3 = read_sql_fut('fut_oi{}'.format(a_list[2]))

    # 豆油
    header_y = draw_separator('豆油')
    # 豆油远期曲线
    future_curve_y = draw_future_curve(y_spot, y_1, y_2, y_3)
    # 豆油一致性预期指标
    idctr_y = draw_spec_idctr('豆油', y_spdrdx, y_index, y_ry)
    # 1. 豆油现货基差表；
    y_spot_chart = draw_spot_spd('豆油', y_spot)
    # 豆油合约价格、成交量、持仓量、持仓量变化图
    y_contract_1 = draw_contract('y{}'.format(a_list[0]), y_1)
    y_contract_2 = draw_contract('y{}'.format(a_list[1]), y_2)
    y_contract_3 = draw_contract('y{}'.format(a_list[2]), y_3)
    # 2. 豆油月差表；
    y_12_chart = draw_spd('y{}'.format(a_list[0]), y_1['close'], 'y{}'.format(a_list[1]), y_2['close'])
    y_23_chart = draw_spd('y{}'.format(a_list[1]), y_2['close'], 'y{}'.format(a_list[2]), y_3['close'])

    # 棕榈油
    header_p = draw_separator('棕榈油')
    # 棕榈油远期曲线
    future_curve_p = draw_future_curve(p_spot, p_1, p_2, p_3)
    # 棕榈油一致性预期指标
    idctr_p = draw_spec_idctr('棕榈油', p_spdrdx, p_index, p_ry)
    # 3. 棕榈油现货基差表；
    p_spot_chart = draw_spot_spd('棕榈油', p_spot)
    # 棕榈油合约价格、成交量、持仓量、持仓量变化图
    p_contract_1 = draw_contract('p{}'.format(a_list[0]), p_1)
    p_contract_2 = draw_contract('p{}'.format(a_list[1]), p_2)
    p_contract_3 = draw_contract('p{}'.format(a_list[2]), p_3)
    # 4. 棕榈油月差表；
    p_12_chart = draw_spd('p{}'.format(a_list[0]), p_1['close'], 'p{}'.format(a_list[1]), p_2['close'])
    p_23_chart = draw_spd('p{}'.format(a_list[1]), p_2['close'], 'p{}'.format(a_list[2]), p_3['close'])

    # 菜油
    header_oi = draw_separator('菜油')
    # 菜油远期曲线
    future_curve_oi = draw_future_curve(oi_spot, oi_1, oi_2, oi_3)
    # 菜油一致性预期指标
    idctr_oi = draw_spec_idctr('菜油', oi_spdrdx, oi_index, oi_ry)
    # 5. 菜油现货基差表；
    oi_spot_chart = draw_spot_spd('菜油', oi_spot)
    # 菜油合约价格、成交量、持仓量、持仓量变化图
    oi_contract_1 = draw_contract('oi{}'.format(a_list[0]), oi_1)
    oi_contract_2 = draw_contract('oi{}'.format(a_list[1]), oi_2)
    oi_contract_3 = draw_contract('oi{}'.format(a_list[2]), oi_3)
    # 6. 菜油月差表；
    oi_12_chart = draw_spd('oi{}'.format(a_list[0]), oi_1['close'], 'oi{}'.format(a_list[1]), oi_2['close'])
    oi_23_chart = draw_spd('oi{}'.format(a_list[1]), oi_2['close'], 'oi{}'.format(a_list[2]), oi_3['close'])

    # 品种间
    header_cross = draw_separator('品种间')

    # 7. 豆棕价差；
    arbi_record('y', y_index['close'], 'p', p_index['close'], x=1, y=1, opt=2, cat=1)
    arbi_record('y', y_spot['spot'], 'p', p_spot['spot'], x=1, y=1, opt=2, cat=2)
    y_p_dif_spot = draw_price_dif('豆油现货', y_spot['spot'], '棕榈油现货', p_spot['spot'], 5)
    y_p_dif_main = draw_price_dif('豆油主连', y_spot['main_price'], '棕榈油主连', p_spot['main_price'], 5)
    y_p_dif_1 = draw_price_dif('y{}'.format(a_list[0]), y_1['close'], 'p{}'.format(a_list[0]), p_1['close'], 5)
    y_p_dif_2 = draw_price_dif('y{}'.format(a_list[1]), y_2['close'], 'p{}'.format(a_list[1]), p_2['close'], 5)
    y_p_dif_3 = draw_price_dif('y{}'.format(a_list[2]), y_3['close'], 'p{}'.format(a_list[2]), p_3['close'], 5)
    # 8. 豆菜价差；
    arbi_record('y', y_index['close'], 'oi', oi_index['close'], x=1, y=1, opt=2, cat=1)
    arbi_record('y', y_spot['spot'], 'oi', oi_spot['spot'], x=1, y=1, opt=2, cat=2)
    y_oi_dif_spot = draw_price_dif('豆油现货', y_spot['spot'], '菜油现货', oi_spot['spot'], 1)
    y_oi_dif_main = draw_price_dif('豆油主连', y_spot['main_price'], '菜油主连', oi_spot['main_price'], 1)
    y_oi_dif_1 = draw_price_dif('y{}'.format(a_list[0]), y_1['close'], 'oi{}'.format(a_list[0]), oi_1['close'], 1)
    y_oi_dif_2 = draw_price_dif('y{}'.format(a_list[1]), y_2['close'], 'oi{}'.format(a_list[1]), oi_2['close'], 1)
    y_oi_dif_3 = draw_price_dif('y{}'.format(a_list[2]), y_3['close'], 'oi{}'.format(a_list[2]), oi_3['close'], 1)
    # 9. 菜棕价差；
    arbi_record('oi', oi_index['close'], 'p', p_index['close'], x=1, y=1, opt=2, cat=1)
    arbi_record('oi', oi_spot['spot'], 'p', p_spot['spot'], x=1, y=1, opt=2, cat=2)
    oi_p_dif_spot = draw_price_dif('菜油现货', oi_spot['spot'], '棕榈油现货', p_spot['spot'])
    oi_p_dif_main = draw_price_dif('菜油主连', oi_spot['main_price'], '棕榈油主连', p_spot['main_price'])
    oi_p_dif_1 = draw_price_dif('oi{}'.format(a_list[0]), oi_1['close'], 'p{}'.format(a_list[0]), p_1['close'])
    oi_p_dif_2 = draw_price_dif('oi{}'.format(a_list[1]), oi_2['close'], 'p{}'.format(a_list[1]), p_2['close'])
    oi_p_dif_3 = draw_price_dif('oi{}'.format(a_list[2]), oi_3['close'], 'p{}'.format(a_list[2]), p_3['close'])

    page = Page(page_title='油脂链', layout=Page.SimplePageLayout)
    page.add(
        header_y,
        future_curve_y,
        idctr_y,
        y_spot_chart,
        y_contract_1,
        y_contract_2,
        y_contract_3,
        y_12_chart,
        y_23_chart,
        header_p,
        future_curve_p,
        idctr_p,
        p_spot_chart,
        p_contract_1,
        p_contract_2,
        p_contract_3,
        p_12_chart,
        p_23_chart,
        header_oi,
        future_curve_oi,
        idctr_oi,
        oi_spot_chart,
        oi_contract_1,
        oi_contract_2,
        oi_contract_3,
        oi_12_chart,
        oi_23_chart,
        header_cross,
        y_p_dif_spot,
        y_p_dif_main,
        y_p_dif_1,
        y_p_dif_2,
        y_p_dif_3,
        y_oi_dif_spot,
        y_oi_dif_main,
        y_oi_dif_1,
        y_oi_dif_2,
        y_oi_dif_3,
        oi_p_dif_spot,
        oi_p_dif_main,
        oi_p_dif_1,
        oi_p_dif_2,
        oi_p_dif_3,
    )
    page.render('油脂链.html')

